package com.yhp.dao.iml;

import com.yhp.bean.Role;
import com.yhp.bean.Users;
import com.yhp.dao.UsersDao;
import com.yhp.util.DbUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UsersDaoImpl extends DbUtils implements UsersDao {
    @Override
    public Users getUsers(String usersName, String passWord) {
        Users users = null;

        try {
            String sql = "select * from users where loginname=? and password=?";
            List list = new ArrayList();
            list.add(usersName);
            list.add(passWord);
            ResultSet resultSet = query(sql, list);
            if (resultSet==null){
                return null;
            }
            while (resultSet.next()){
                users = new Users();
                users.setLoginName(resultSet.getString("loginname"));
                users.setRealName(resultSet.getString("realname"));
                users.setPassword(resultSet.getString("password"));
                users.setUserId(resultSet.getInt("userid"));
                users.setRoleId(resultSet.getInt("roleid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return users;
    }

    @Override
    public List<Users> getUsersList(int pageIndex, int pageSize) {
        List usersList = new ArrayList();
        try {
            String sql = "select * from users u,role r where u.roleid=r.roleid limit ?,?";
            List params = new ArrayList();
            params.add((pageIndex-1)*pageSize);
            params.add(pageSize);
            resultSet = query(sql, params);
            while (resultSet.next()){
                Users users = new Users();
                users.setUserId(resultSet.getInt("userid"));
                users.setLoginName(resultSet.getString("loginname"));
                users.setUserId(resultSet.getInt("userid"));
                users.setRealName(resultSet.getString("realname"));
                Role role = new Role();
                role.setRoleName(resultSet.getString("rolename"));
                role.setRoleId(resultSet.getInt("roleid"));
                users.setRole(role);
                usersList.add(users);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return usersList;
    }

    @Override
    public int total() {
        int total=0;
        try {
            String sql = "select count(*) from users u,role r where u.roleid=r.roleid";

            resultSet = query(sql, null);
            while (resultSet.next()){
              total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public int addUser(Users users) {
        int update = 0;
        try {
            String sql = "insert into users values(null,?,?,?,?,?,?,?,?,?,?)";
            List params = new ArrayList();
            params.add(users.getLoginName());
            params.add(users.getPassword());
            params.add(users.getRealName());
            params.add(users.getSex());
            params.add(users.getEmail());
            params.add(users.getAddress());
            params.add(users.getPhone());
            params.add(users.getCardId());
            params.add(users.getDesc());
            params.add(users.getRoleId());
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public Users findById(int userId) {
        Users users = new Users();
        try {
            String sql = "select * from users where userid=?";
            List params = new ArrayList();
            params.add(userId);
            resultSet = query(sql, params);
            while (resultSet.next()){
                users.setLoginName(resultSet.getString("loginname"));
                users.setPassword(resultSet.getString("password"));
                users.setRealName(resultSet.getString("realname"));
                users.setSex(resultSet.getInt("sex"));
                users.setEmail(resultSet.getString("email"));
                users.setAddress(resultSet.getString("address"));
                users.setPhone(resultSet.getString("phone"));
                users.setCardId(resultSet.getString("cardid"));
                users.setDesc(resultSet.getString("descr"));
                users.setRoleId(resultSet.getInt("roleid"));
                users.setUserId(resultSet.getInt("userid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return users;
    }

    @Override
    public int updateUser(Users users) {
        int update = 0;
        try {
            String sql = "update users set loginname=?,password=?,realname=?,sex=?,email=?,address=?,phone=?,cardid=?,descr=?,roleid=? where userid=?";
            List params = new ArrayList();
            params.add(users.getLoginName());
            params.add(users.getPassword());
            params.add(users.getRealName());
            params.add(users.getSex());
            params.add(users.getEmail());
            params.add(users.getAddress());
            params.add(users.getPhone());
            params.add(users.getCardId());
            params.add(users.getDesc());
            params.add(users.getRoleId());
            params.add(users.getUserId());
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int deleteUserById(int userId) {
        int update = 0;
        try {
            String sql = "delete from users where userid=?";
            List params = new ArrayList();
            params.add(userId);
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return update;
    }
}
